package com.xueshandai.db.migration;

import com.xueshandai.db.migration.database.DataBase;
import com.xueshandai.db.migration.database.implement.DataBaseMSSQLImpl;
import com.xueshandai.db.migration.database.implement.DataBaseMySQLImpl;
import com.xueshandai.db.migration.mode.LineData;
import com.xueshandai.db.migration.mode.config.FieldMap;
import com.xueshandai.db.migration.mode.config.TableMap;
import com.xueshandai.db.migration.persistence.implement.AccessFileImpl;
import com.xueshandai.db.migration.persistence.implement.AccessMSSQLImpl;
import com.xueshandai.db.migration.persistence.implement.AccessMySQLImpl;
import com.xueshandai.db.migration.tool.FileUtil;
import com.xueshandai.db.migration.tool.TableMapUtil;
import org.junit.Test;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by dengzt on 2015/1/12.
 */
public class DataMigration {

    /**
     * 自动生成表和字段的映射文件
     */
    @Test
    public void createConfFieldMap() {
        DataBase msSql = new DataBaseMSSQLImpl("127.0.0.1", 1433, "sa", "1", "xueshandai");
        DataBase mySql = new DataBaseMySQLImpl("127.0.0.1", 3306, "root", "123456", "xueshandai");
        List<String> msSqlAllTables = msSql.getAllTables();
        List<String> mySqlAllTables = mySql.getAllTables();

        for (String msTableName : msSqlAllTables) {
            for (String myTableName : mySqlAllTables) {
                //member==leaguer 或者是他们开头的表
                if ( msTableName.equals(myTableName) || ( "member".equals(msTableName) && "leaguer".equals(myTableName) ) || ( msTableName.replace("member", "").equals(myTableName.replace("leaguer", "")) ) ) {
                    try {
                        List<String> msFieldList = new ArrayList<String>();
                        Connection msSqlConnection = msSql.getConnection();
                        PreparedStatement msPreparedStatement = msSqlConnection.prepareStatement("select * from "+msTableName);
                        ResultSet msResultSet = msPreparedStatement.executeQuery();
                        ResultSetMetaData msResultSetMetaData = msResultSet.getMetaData();
                        int msColumnCount = msResultSetMetaData.getColumnCount();
                        for (int i=1; i<=msColumnCount; i++) {
                            msFieldList.add(msResultSetMetaData.getColumnName(i));
                        }

                        List<String> myFieldList = new ArrayList<String>();
                        Connection mySqlConnection = mySql.getConnection();
                        PreparedStatement myPreparedStatement = mySqlConnection.prepareStatement("select * from " +myTableName);
                        ResultSet myResultSet = myPreparedStatement.executeQuery();
                        ResultSetMetaData myResultSetMetaData = myResultSet.getMetaData();
                        int myColumnCount = myResultSetMetaData.getColumnCount();
                        for (int i=1; i<=myColumnCount; i++) {
                            String fieldName = myResultSetMetaData.getColumnName(i);

                            if ( "id".equals(fieldName) ) {

                            } else {
                                myFieldList.add(fieldName);
                            }
                        }

                        /**
                         * 找到所有相同的字段
                         */
                        List<FieldMap> fieldMapListSame = new ArrayList<FieldMap>();
                        for (int i=0; i<msFieldList.size(); i++) {
                            for (int j=0; j<myFieldList.size(); j++) {
                                String msField = msFieldList.get(i);
                                String myField = myFieldList.get(j);
                                if ( msField.equals(myField) ) {
                                    FieldMap fieldMap = new FieldMap();
                                    fieldMap.setOldField(msField);
                                    fieldMap.setNewField(myField);
                                    fieldMapListSame.add(fieldMap);
                                }
                            }
                        }

                        /**
                         * 添加默认字段映射
                         */
                        FieldMap fieldMap_id = new FieldMap();
                        fieldMap_id.setOldField("id");
                        fieldMap_id.setNewField(myTableName + "_" + "id");

                        FieldMap fieldMap_date_created = new FieldMap();
                        fieldMap_date_created.setOldField("date_created");
                        fieldMap_date_created.setNewField("created_date");

                        FieldMap fieldMap_last_updated = new FieldMap();
                        fieldMap_last_updated.setOldField("last_updated");
                        fieldMap_last_updated.setNewField("updated_date");

                        TableMap tableMap = new TableMap();
                        tableMap.setOldTableName(msTableName);
                        tableMap.setNewTableName(myTableName);
                        tableMap.setFieldMapList(fieldMapListSame);
                        tableMap.getFieldMapList().add(fieldMap_id);
                        tableMap.getFieldMapList().add(fieldMap_date_created);
                        tableMap.getFieldMapList().add(fieldMap_last_updated);

                        File file = null;
                        FileWriter fileWriter = null;
                        BufferedWriter bufferedWriter = null;
                        try {
                            file = FileUtil.createFileInConfDirInUserDir(msTableName + "_" + myTableName);
                            fileWriter = new FileWriter(file);
                            bufferedWriter = new BufferedWriter(fileWriter);
                            bufferedWriter.write(tableMap.toJSON());
                            bufferedWriter.flush();
                            fileWriter.flush();
                        } catch (IOException e) {
                            e.printStackTrace();
                        } finally {
                            if ( bufferedWriter != null ) try {
                                bufferedWriter.close();
                            } catch (IOException e) {
                                e.printStackTrace();
                            }
                            if ( fileWriter != null ) try {
                                fileWriter.close();
                            } catch (IOException e) {
                                e.printStackTrace();
                            }
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

    /**
     * 将数据库中的数据迁移到文件中
     */
    @Test
    public void databaseDataToFile() {
        DataBase dataBase = new DataBaseMSSQLImpl("127.0.0.1", 1433, "sa", "1", "xueshandai");
        List<String> tableNameList = dataBase.getAllTables();
        for (String tableName : tableNameList) {
            File file = FileUtil.createFileInTempDirInUserDir(tableName);
            AccessFileImpl  accessFile = new AccessFileImpl(file);
            AccessMSSQLImpl accessMSSQL = new AccessMSSQLImpl(dataBase.getConnection(), tableName);
            while (accessMSSQL.next()) {
                LineData lineData = accessMSSQL.readLine();
                accessFile.writeLine(lineData);
            }
            accessFile.destroy();
            accessMSSQL.destroy();
        }
    }

    /**
     * 将文件中的数据迁移到数据库中
     */
    @Test
    public void fileToDatabase() {
        DataBase mySql = new DataBaseMySQLImpl("127.0.0.1", 3306, "root", "123456", "xueshandai");
        TableMapUtil tableMapUtil = TableMapUtil.getInstance();
        List<TableMap> tableMapList = tableMapUtil.getTableMapList();
        for (TableMap tableMap : tableMapList) {
            String oldTableName = tableMap.getOldTableName();
            if ( true ) {
                File tableFile = FileUtil.getFileInTempDirInUserDir(oldTableName);
                AccessFileImpl accessFile = new AccessFileImpl(tableFile);
                AccessMySQLImpl accessMySQL = new AccessMySQLImpl(mySql.getConnection(), tableMap);
                while (accessFile.next()) {
                    accessMySQL.writeLine(accessFile.readLine());
                }
                accessFile.destroy();
                accessMySQL.destroy();
            }
        }
    }

}
